"""
-------------------------------
- File_Name: oracleDBPool_final.py
- Date: 2025/2/16 (Updated)
- Author: yangzhide
- Email: xxzjyzd@126.com
- Description: oracle数据库连接池 (采用手动结果转换方案)
-------------------------------
"""
from fastapi import HTTPException
import cx_Oracle
from cx_Oracle import IntegrityError
from typing import Tuple, Optional, Dict, Any, List, Callable

class OracleDBPool:
    """Oracle 连接池管理 (支持自动提交、回滚和连接释放)"""

    def __init__(self, config: dict, pool_name: str = "default_pool"):
        """
        初始化连接池。

        Args:
            config (dict): 数据库连接配置字典。
            pool_name (str): 连接池的名称，用于日志和调试。
        """
        self.pool_name = pool_name
        self.user = config.get('user')
        self.password = config.get('password')
        self.min_conn = config.get('min', 2)
        self.max_conn = config.get('max', 5)
        self.increment = config.get('increment', 1)
        self.encoding = config.get('encoding', 'UTF-8')

        self.host = config.get('host')
        self.port = config.get('port')
        if not self.host or not self.port:
            raise ValueError(f"连接池 '{self.pool_name}' 的配置缺少 'host' 或 'port' 参数。")

        if "service_name" in config and config['service_name']:
            dsn_str = cx_Oracle.makedsn(self.host, self.port, service_name=config['service_name'])
        elif "sid" in config and config['sid']:
            dsn_str = cx_Oracle.makedsn(self.host, self.port, sid=config['sid'])
        else:
            raise ValueError(f"连接池 '{self.pool_name}' 的配置必须提供 'service_name' 或 'sid'。")

        try:
            self.pool = cx_Oracle.SessionPool(
                user=self.user,
                password=self.password,
                dsn=dsn_str,
                min=self.min_conn,
                max=self.max_conn,
                increment=self.increment,
                encoding=self.encoding,
                threaded=True,
            )
            # print(f"Oracle 连接池 '{self.pool_name}' (DSN: {dsn_str}) 初始化成功。")
        except cx_Oracle.DatabaseError as e:
            print(f"创建 Oracle 连接池 '{self.pool_name}' (DSN: {dsn_str}) 失败: {e}")
            raise

        self.conn: Optional[cx_Oracle.Connection] = None
        self.cursor: Optional[cx_Oracle.Cursor] = None

    def get_conn(self) -> Tuple[cx_Oracle.Connection, cx_Oracle.Cursor]:
        """
        从连接池获取连接和游标。
        注意：由于 rowfactory 在此环境中未按预期工作，结果转换将在 execute_query 中手动进行。
        """
        if not self.pool:
            raise RuntimeError(f"连接池 '{self.pool_name}' 未初始化或已关闭。")
        conn = self.pool.acquire()
        cursor = conn.cursor()
        return conn, cursor

    def release_conn(self, conn: cx_Oracle.Connection, cursor: Optional[cx_Oracle.Cursor] = None) -> None:
        """释放连接回池，并关闭游标"""
        if cursor:
            try:
                cursor.close()
            except cx_Oracle.DatabaseError as e:
                print(f"警告: 关闭游标时发生错误 (连接池 '{self.pool_name}'): {e}")
        if conn:
            try:
                self.pool.release(conn)
            except cx_Oracle.DatabaseError as e:
                print(f"警告: 释放连接到池 '{self.pool_name}' 时发生错误: {e}")

    def __enter__(self) -> Tuple[cx_Oracle.Connection, cx_Oracle.Cursor]:
        """上下文管理器支持 (用于单一数据库的事务操作)"""
        if not self.pool:
            raise RuntimeError(f"连接池 '{self.pool_name}' 未初始化或已关闭，无法进入上下文。")
        self.conn, self.cursor = self.get_conn()
        return self.conn, self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """退出上下文时自动提交/回滚并释放连接"""
        if not self.conn:
            if self.cursor:
                try: self.cursor.close()
                except: pass
            return

        try:
            if exc_type:
                self.conn.rollback()
            else:
                self.conn.commit()
        except cx_Oracle.DatabaseError as db_err:
            print(f"错误: 在连接池 '{self.pool_name}' 的 __exit__ 中提交/回滚事务失败: {db_err}")
            if not exc_type:
                raise
        finally:
            self.release_conn(self.conn, self.cursor)
            self.conn = None
            self.cursor = None

    def execute_query(self, sql: str, params: Optional[Dict[str, Any]] = None) -> List[Dict[str, Any]]:
        """
        执行查询语句 (SELECT) 并返回所有结果。
        结果是字典列表，键为小写列名（通过手动转换实现）。
        """
        with self as (conn_from_with, cursor_from_with): # 使用了 __enter__ 和 __exit__
            cursor_from_with.execute(sql, params or {})

            if not cursor_from_with.description:
                # 如果没有列描述（例如，非查询语句或查询无结果的特殊情况），返回空列表
                return []

            # 获取列名并转为小写
            column_names = [col[0].lower() for col in cursor_from_with.description]

            # 获取所有结果行（元组形式）
            tuple_results = cursor_from_with.fetchall()

            # 手动将每行元组转换为字典
            dict_results = [dict(zip(column_names, row)) for row in tuple_results]

            return dict_results

    # 针对单数据库，单个dml语句执行
    def execute_dml(self, sql: str, params: Optional[Dict[str, Any]] = None, auto_commit: bool = True) -> int:
        """
        执行 DML 语句 (INSERT, UPDATE, DELETE)。

        Args:
            sql (str): SQL语句。
            params (Optional[Dict[str, Any]]): SQL参数。
            auto_commit (bool): 是否自动提交。如果为 False，则需要外部管理事务。

        Returns:
            int: 受影响的行数。
        """
        conn, cursor = None, None
        try:
            conn, cursor = self.get_conn() # 单独获取连接，不通过 __enter__
            cursor.execute(sql, params or {})
            rowcount = cursor.rowcount
            if auto_commit:
                conn.commit()
            return rowcount
        except IntegrityError:
            raise HTTPException(status_code=501,detail=f'违反唯一约束,sql:{sql}, data:{params}')
        except Exception as e:
            if conn and auto_commit:
                try:
                    conn.rollback()
                except cx_Oracle.DatabaseError as rb_err:
                     print(f"警告: DML执行错误后回滚失败 (连接池 '{self.pool_name}'): {rb_err}")
            print(f"错误: DML 执行失败 (连接池 '{self.pool_name}'): {e}, SQL: {sql}")
            raise
        finally:
            if conn: # 确保释放获取的连接
                self.release_conn(conn, cursor)

    # 针对单数据库，单个dml语句执行
    def execute_dml_many(self, sql: str, params_list: List[Dict[str, Any]], auto_commit: bool = True) -> int:
        """
        批量执行 DML 语句。

        Args:
            sql (str): SQL语句。
            params_list (List[Dict[str, Any]]): 参数列表，每个元素是一个字典。
            auto_commit (bool): 是否自动提交。

        Returns:
            int: 总共受影响的行数。
        """
        conn, cursor = None, None
        if not params_list:
            return 0
        try:
            conn, cursor = self.get_conn() # 单独获取连接
            cursor.executemany(sql, params_list)
            rowcount = cursor.rowcount
            if auto_commit:
                conn.commit()
            return rowcount
        except Exception as e:
            if conn and auto_commit:
                try:
                    conn.rollback()
                except cx_Oracle.DatabaseError as rb_err:
                    print(f"警告: 批量DML执行错误后回滚失败 (连接池 '{self.pool_name}'): {rb_err}")
            print(f"错误: 批量 DML 执行失败 (连接池 '{self.pool_name}'): {e}, SQL: {sql}")
            raise
        finally:
            if conn: # 确保释放获取的连接
                self.release_conn(conn, cursor)

    def close_pool(self) -> None:
        """关闭连接池。在应用程序关闭时调用。"""
        if self.pool:
            try:
                self.pool.close()
                print(f"Oracle 连接池 '{self.pool_name}' 已成功关闭。")
            except cx_Oracle.DatabaseError as e:
                print(f"错误: 关闭 Oracle 连接池 '{self.pool_name}' 失败: {e}")
            self.pool = None


# --- MultiDBManager 类 (这个类不需要修改，它依赖于 OracleDBPool 的接口) ---
DBConnectionsMap = Dict[str, Tuple[cx_Oracle.Connection, cx_Oracle.Cursor]]

class MultiDBManager:
    """
    管理多个 OracleDBPool 实例，并为跨数据库操作提供事务一致性的尽力而为支持。
    """
    def __init__(self, db_configs: Dict[str, Dict[str, Any]]):
        self.pools: Dict[str, OracleDBPool] = {}
        for name, config in db_configs.items():
            try:
                self.pools[name] = OracleDBPool(config, pool_name=name)
            except Exception as e:
                print(f"严重警告: 初始化数据库连接池 '{name}' 失败: {e}。该连接池将不可用。")

    def get_pool(self, db_name: str) -> Optional[OracleDBPool]:
        pool = self.pools.get(db_name)
        if not pool:
            print(f"警告: 未找到名为 '{db_name}' 的数据库连接池。")
        return pool

    def transactional_operation(
        self,
        db_names: List[str],
        operation_callback: Callable[[DBConnectionsMap], Any]
    ) -> Any:
        active_connections: DBConnectionsMap = {}
        acquired_resources: List[Tuple[OracleDBPool, cx_Oracle.Connection, cx_Oracle.Cursor]] = []
        try:
            for name in db_names:
                pool = self.get_pool(name)
                if not pool:
                    raise ValueError(f"数据库连接池 '{name}' 未配置或初始化失败。")
                conn, cursor = pool.get_conn() # OracleDBPool.get_conn() 被调用
                active_connections[name] = (conn, cursor)
                acquired_resources.append((pool, conn, cursor))
            result = operation_callback(active_connections)
            for name, (conn, _) in active_connections.items():
                try:
                    conn.commit()
                except cx_Oracle.DatabaseError as commit_err:
                    print(f"错误: 提交数据库 '{name}' 的事务失败: {commit_err}。将尝试回滚所有。")
                    raise
            return result
        except Exception as e:
            print(f"事务操作中发生异常: {e}。正在回滚所有涉及的数据库...")
            for name, (conn, _) in active_connections.items():
                try:
                    conn.rollback()
                except cx_Oracle.DatabaseError as rollback_err:
                    print(f"严重错误: 回滚数据库 '{name}' 的事务失败: {rollback_err}")
            raise
        finally:
            for pool_instance, conn, cursor in acquired_resources:
                pool_instance.release_conn(conn, cursor)

    def shutdown(self):
        print("正在关闭 MultiDBManager 中的所有连接池...")
        for name, pool in self.pools.items():
            pool.close_pool()
        print("MultiDBManager 所有连接池关闭完成。")


# --- 示例用法 ---
if __name__ == "__main__":
    # 替换为您的实际 Oracle 凭据和 DSN 详细信息
    ORACLE_DB_CONFIG_EXAMPLE = {
        "lmis_17": {
        "user": "lmis",
        "password": "lmis9system",
        "host": "192.168.0.17",
        "port": 1521,
        "sid": "lmis",
        "service_name": "lmis",  # 默认使用service_name
        "min": 2,  # 保持的最小会话数
        "max": 10,  # 允许的最大会话数
        "increment": 1,  # 当池中会话不足时，一次性创建的额外会话数
        "encoding": "UTF-8"

    },
        # 可以添加其他数据库配置
        # "archive_db": { ... }
    }

    # 检查是否是占位符配置
    is_placeholder_config = (ORACLE_DB_CONFIG_EXAMPLE["lmis_17"]["user"] == "your_user")

    if is_placeholder_config:
        print("示例配置是占位符，跳过数据库操作测试。请更新实际凭据后运行。")
    else:
        print("\n--- 测试 OracleDBPool (手动转换方案) ---")
        db_pool_lmis = None
        try:
            db_pool_lmis = OracleDBPool(ORACLE_DB_CONFIG_EXAMPLE['lmis_17'], pool_name='lmis_pool_test')

            # 测试 execute_query
            print("测试 execute_query:")
            # 假设 new_jc_hzzl 表存在且有数据
            # CREATE TABLE new_jc_hzzl (yez_id VARCHAR2(20), yez_name VARCHAR2(100));
            # INSERT INTO new_jc_hzzl VALUES ('1001', '公司A');
            # INSERT INTO new_jc_hzzl VALUES ('1007', '公司B');
            # COMMIT;
            query = "select t.yez_id, t.yez_name from new_jc_hzzl t where t.yez_id in ('1001','1007','1009')"
            results = db_pool_lmis.execute_query(query)
            print(f"查询 '{query}' 的结果:")
            if results:
                for row in results:
                    print(f"  {row} (类型: {type(row)})")
                    assert isinstance(row, dict), "查询结果应为字典"
                    assert 'yez_id' in row and 'yez_name' in row, "字典键应为小写列名"
            else:
                print("  未查询到结果或表不存在/无数据。")

            # 测试 execute_dml (INSERT)
            print("\n测试 execute_dml (INSERT):")
            # 确保测试表存在或创建它
            try:
                with db_pool_lmis as (conn, cursor): # 使用上下文管理器执行DDL
                    try:
                        cursor.execute("CREATE TABLE temp_test_table (id NUMBER, name VARCHAR2(50))")
                        print("  表 'temp_test_table' 已创建。")
                    except cx_Oracle.DatabaseError as e:
                        if "ORA-00955" in str(e): # 表已存在
                             print("  表 'temp_test_table' 已存在。")
                        else:
                            raise
            except Exception as e:
                 print(f"  创建测试表时出错: {e}")


            insert_sql = "INSERT INTO temp_test_table (id, name) VALUES (:id, :name)"
            insert_params = {"id": 1, "name": "测试条目1"}
            affected_rows = db_pool_lmis.execute_dml(insert_sql, insert_params)
            print(f"  执行 '{insert_sql}' 插入了 {affected_rows} 行。")

            # 验证插入
            verify_results = db_pool_lmis.execute_query("SELECT id, name FROM temp_test_table WHERE id = 1")
            print(f"  验证插入结果: {verify_results}")
            assert len(verify_results) == 1 and verify_results[0]['name'] == "测试条目1"

            # 测试 execute_dml_many (批量 INSERT)
            print("\n测试 execute_dml_many:")
            batch_params = [
                {"id": 2, "name": "批量条目2"},
                {"id": 3, "name": "批量条目3"}
            ]
            affected_rows_many = db_pool_lmis.execute_dml_many(insert_sql, batch_params)
            print(f"  执行批量插入影响了 {affected_rows_many} 行。")
            verify_batch = db_pool_lmis.execute_query("SELECT COUNT(*) AS count FROM temp_test_table WHERE id IN (2,3)")
            print(f"  验证批量插入: {verify_batch}")
            assert verify_batch[0]['count'] == 2


            # 测试上下文管理器的事务 (成功提交)
            print("\n测试上下文管理器 (成功提交):")
            try:
                with db_pool_lmis as (conn, cursor):
                    cursor.execute("INSERT INTO temp_test_table (id, name) VALUES (:1, :2)", (4, "上下文条目4"))
                    print("  在 'with' 块中插入数据 (id=4)。")
                # 退出 'with' 块时应自动提交
                verify_with_commit = db_pool_lmis.execute_query("SELECT name FROM temp_test_table WHERE id = 4")
                print(f"  验证上下文提交结果: {verify_with_commit}")
                assert len(verify_with_commit) == 1 and verify_with_commit[0]['name'] == "上下文条目4"
            except Exception as e:
                print(f"  上下文管理器测试出错: {e}")

            # 测试上下文管理器的事务 (异常回滚)
            print("\n测试上下文管理器 (异常回滚):")
            try:
                with db_pool_lmis as (conn, cursor):
                    cursor.execute("INSERT INTO temp_test_table (id, name) VALUES (:1, :2)", (5, "将回滚的条目5"))
                    print("  在 'with' 块中插入数据 (id=5)。")
                    raise ValueError("模拟一个错误以触发回滚")
            except ValueError as ve:
                print(f"  捕获到预期的错误: {ve}")
            except Exception as e:
                print(f"  上下文回滚测试中发生意外错误: {e}")

            verify_with_rollback = db_pool_lmis.execute_query("SELECT name FROM temp_test_table WHERE id = 5")
            print(f"  验证上下文回滚结果 (id=5应不存在): {verify_with_rollback}")
            assert len(verify_with_rollback) == 0, "id=5 的条目应已被回滚"

            # 清理测试表
            print("\n清理测试表...")
            db_pool_lmis.execute_dml("DROP TABLE temp_test_table")
            print("  表 'temp_test_table' 已删除。")


        except cx_Oracle.DatabaseError as db_err:
            print(f"数据库操作测试中发生错误: {db_err}")
            import traceback
            traceback.print_exc()
        except Exception as ex:
            print(f"测试中发生一般错误: {ex}")
            import traceback
            traceback.print_exc()
        finally:
            if db_pool_lmis:
                db_pool_lmis.close_pool()


    print("\n脚本执行完毕。")